package org.nutz.dao.jdbc;

import java.io.File;
import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Calendar;

import javax.sql.DataSource;

import org.nutz.castor.Castors;
import org.nutz.dao.entity.annotation.ColType;
import org.nutz.dao.impl.entity.field.NutMappingField;
import org.nutz.dao.impl.jdbc.BlobValueAdaptor;
import org.nutz.dao.impl.jdbc.ClobValueAdaptor;
import org.nutz.json.Json;
import org.nutz.lang.Files;
import org.nutz.lang.Lang;
import org.nutz.lang.Mirror;
import org.nutz.lang.Streams;
import org.nutz.lang.meta.Email;
import org.nutz.log.Log;
import org.nutz.log.Logs;

/**
 * 提供一些与 JDBC 有关的帮助函数
 * 
 * @author zozoh(zozohtnt@gmail.com)
 */
public abstract class Jdbcs {

	private static final Log log = Logs.get();

	private static final JdbcExpertConfigFile conf;

	/*
	 * 根据配置文件获取 experts 的列表
	 */
	static {
		// 看看有没有用户自定义的映射文件
		File f = Files.findFile("nutz_jdbc_experts.js");//TODO 不可配置??
		// 如果没有则使用默认的映射文件
		if (null == f) {
			conf = Json.fromJson(JdbcExpertConfigFile.class, Streams.fileInr("org/nutz/dao/jdbc/nutz_jdbc_experts.js")).init();
		} else
			conf = Json.fromJson(JdbcExpertConfigFile.class, Streams.fileInr("nutz_jdbc_experts.js")).init();
		try {
			for (String key : conf.getExperts().keySet()) {
				// 检查一下正则表达式是否正确
				//在conf类中自行检查
				//Pattern.compile(key,Pattern.DOTALL & Pattern.CASE_INSENSITIVE);
				// 检查一下是否可以生成 Expert 的实例
				conf.getExpert(key);//TODO 值得商讨
			}
		}
		catch (Exception e) {
			throw Lang.wrapThrow(e);
		}
	}

	/**
	 * 针对一个数据源，返回其专属的 JdbcExpert
	 * 
	 * @param ds
	 *            数据源
	 * @return 该数据库的特殊驱动封装类
	 * 
	 * @see org.nutz.dao.jdbc.Jdbcs#getExpert(String, String)
	 */
	public static JdbcExpert getExpert(DataSource ds) {
		Connection conn = null;
		try {
			conn = ds.getConnection();
			DatabaseMetaData meta = conn.getMetaData();
			String pnm = meta.getDatabaseProductName();
			String ver = meta.getDatabaseProductVersion();
			return getExpert(pnm, ver);
		}
		catch (Throwable e) {
			throw Lang.wrapThrow(e);
		}
		finally {
			if (null != conn)
				try {
					conn.close();
				}
				catch (SQLException e) {}
		}
	}

	/**
	 * 根据数据库的产品名称，获取其专属的 Expert
	 * <p>
	 * 映射的规则存放在 JSON 文件 "nutz_jdbc_experts.js" 中，你可以通过建立这个文件修改 Nutz 的默认映射规则
	 * <p>
	 * 比如下面的文件，将支持两种数据库
	 * 
	 * <pre>
	 * {
	 *   experts : {
	 *     "postgresql.*" : "org.nutz.dao.impl.jdbc.psql.PostgresqlExpert",
	 *     "mysql.*" :  "org.nutz.dao.impl.jdbc.mysql.MysqlExpert"
	 *   },
	 *   config : {
	 *     "temp-home" : "~/.nutz/tmp/dao/",
	 *     "temp-max" : 2000
	 *   }
	 * }
	 * </pre>
	 * 
	 * 本函数传入的两个参数将会被:
	 * 
	 * <pre>
	 * String.format(&quot;%s::NUTZ_JDBC::%s&quot;, productName, version);
	 * </pre>
	 * 
	 * 并被你声明的正则表达式(expert 段下的键值)依次匹配，如果匹配上了，就会用相应的类当作驱动封装类
	 * 
	 * @param productName
	 *            数据库产品名称
	 * @param version
	 *            数据库版本号
	 * 
	 * @return 该数据库的特殊驱动封装类
	 * 
	 * @see java.sql.Connection#getMetaData()
	 * @see java.sql.DatabaseMetaData#getDatabaseProductName()
	 */
	public static JdbcExpert getExpert(String productName, String version) {
		String dbName = String.format("%s::NUTZ_JDBC::%s", productName, version).toLowerCase();

		JdbcExpert re = conf.matchExpert(dbName);

		if (null == re)
			throw Lang.makeThrow("Can not support database '%s %s'", productName, version);

		return re;
	}

	public static ValueAdaptor getAdaptorBy(Object obj) {
		if (null == obj)
			return Adaptor.asNull;
		return getAdaptor(Mirror.me(obj));
	}

	public static ValueAdaptor getAdaptor(Mirror<?> mirror) {
		// String and char
		if (mirror.isStringLike())
			return Jdbcs.Adaptor.asString;
		// Int
		if (mirror.isInt())
			return Jdbcs.Adaptor.asInteger;
		// Boolean
		if (mirror.isBoolean())
			return Jdbcs.Adaptor.asBoolean;
		// Long
		if (mirror.isLong())
			return Jdbcs.Adaptor.asLong;
		// Enum
		if (mirror.isEnum())
			return Jdbcs.Adaptor.asEnumChar;
		// Char
		if (mirror.isChar())
			return Jdbcs.Adaptor.asString;
		// Timestamp
		if (mirror.isOf(Timestamp.class))
			return Jdbcs.Adaptor.asTimestamp;
		// Byte
		if (mirror.isByte())
			return Jdbcs.Adaptor.asByte;
		// Short
		if (mirror.isShort())
			return Jdbcs.Adaptor.asShort;
		// Float
		if (mirror.isFloat())
			return Jdbcs.Adaptor.asFloat;
		// Double
		if (mirror.isDouble())
			return Jdbcs.Adaptor.asDouble;
		// BigDecimal
		if (mirror.isOf(BigDecimal.class))
			return Jdbcs.Adaptor.asBigDecimal;
		// Calendar
		if (mirror.isOf(Calendar.class))
			return Jdbcs.Adaptor.asCalendar;
		// java.util.Date
		if (mirror.isOf(java.util.Date.class))
			return Jdbcs.Adaptor.asDate;
		// java.sql.Date
		if (mirror.isOf(java.sql.Date.class))
			return Jdbcs.Adaptor.asSqlDate;
		// java.sql.Time
		if (mirror.isOf(java.sql.Time.class))
			return Jdbcs.Adaptor.asSqlTime;
		// Blob
		if (mirror.isOf(Blob.class))
			return new BlobValueAdaptor(conf.getPool());
		// Clob
		if (mirror.isOf(Clob.class))
			return new ClobValueAdaptor(conf.getPool());

		// 默认情况
		return Jdbcs.Adaptor.asString;
	}

	public static class Adaptor {
		/**
		 * 空值适配器
		 */
		public static final ValueAdaptor asNull = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				return null;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				stat.setNull(i, Types.NULL);
			};

		};

		/**
		 * 字符串适配器
		 */
		public static final ValueAdaptor asString = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				return rs.getString(colName);
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setString(i, null);
				} else {
					stat.setString(i, obj.toString());
				}
			}
		};

		/**
		 * 字符适配器
		 */
		public static final ValueAdaptor asChar = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				return rs.getCharacterStream(colName);
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setString(i, null);
				} else {
					String s;
					if (obj instanceof Character) {
						int c = ((Character) obj).charValue();
						if (c >= 0 && c <= 32)
							s = " ";
						else
							s = String.valueOf((char) c);
					} else
						s = obj.toString();
					stat.setString(i, s);
				}
			}
		};

		/**
		 * 整型适配器
		 */
		public static final ValueAdaptor asInteger = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				int re = rs.getInt(colName);
				return rs.wasNull() ? null : re;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.INTEGER);
				} else {
					int v;
					if (obj instanceof Number)
						v = ((Number) obj).intValue();
					else
						v = Castors.me().castTo(obj.toString(), int.class);
					stat.setInt(i, v);
				}
			}
		};

		/**
		 * 大数适配器
		 */
		public static final ValueAdaptor asBigDecimal = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				return rs.getBigDecimal(colName);
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.BIGINT);
				} else {
					BigDecimal v;
					if (obj instanceof BigDecimal)
						v = (BigDecimal) obj;
					else if (obj instanceof Number)
						v = BigDecimal.valueOf(((Number) obj).longValue());
					else
						v = new BigDecimal(obj.toString());
					stat.setBigDecimal(i, v);
				}
			}
		};

		/**
		 * 布尔适配器
		 */
		public static final ValueAdaptor asBoolean = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				boolean re = rs.getBoolean(colName);
				return rs.wasNull() ? null : re;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					/*
					 * 对 Oracle，Types.BOOLEAN 对于 setNull 是不工作的
					 * 其他的数据库都没有这个问题，所以，只好把类型设成 INTEGER了
					 */
					stat.setNull(i, Types.INTEGER);
				} else {
					boolean v;
					if (obj instanceof Boolean)
						v = (Boolean) obj;
					else if (obj instanceof Number)
						v = ((Number) obj).intValue() > 0;
					else if (obj instanceof Character)
						v = Character.toUpperCase((Character) obj) == 'T';
					else
						v = Boolean.valueOf(obj.toString());
					stat.setBoolean(i, v);
				}
			}
		};

		/**
		 * 长整适配器
		 */
		public static final ValueAdaptor asLong = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				long re = rs.getLong(colName);
				return rs.wasNull() ? null : re;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.INTEGER);
				} else {
					long v;
					if (obj instanceof Number)
						v = ((Number) obj).longValue();
					else
						v = Castors.me().castTo(obj.toString(), long.class);
					stat.setLong(i, v);
				}
			}
		};

		/**
		 * 字节适配器
		 */
		public static final ValueAdaptor asByte = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				byte re = rs.getByte(colName);
				return rs.wasNull() ? null : re;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.TINYINT);
				} else {
					byte v;
					if (obj instanceof Number)
						v = ((Number) obj).byteValue();
					else
						v = Castors.me().castTo(obj.toString(), byte.class);
					stat.setByte(i, v);
				}
			}
		};

		/**
		 * 短整型适配器
		 */
		public static final ValueAdaptor asShort = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				short re = rs.getShort(colName);
				return rs.wasNull() ? null : re;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.SMALLINT);
				} else {
					short v;
					if (obj instanceof Number)
						v = ((Number) obj).shortValue();
					else
						v = Castors.me().castTo(obj.toString(), short.class);
					stat.setShort(i, v);
				}
			}
		};

		/**
		 * 浮点适配器
		 */
		public static final ValueAdaptor asFloat = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				float re = rs.getFloat(colName);
				return rs.wasNull() ? null : re;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.FLOAT);
				} else {
					float v;
					if (obj instanceof Number)
						v = ((Number) obj).floatValue();
					else
						v = Castors.me().castTo(obj.toString(), float.class);
					stat.setFloat(i, v);
				}
			}
		};

		/**
		 * 双精度浮点适配器
		 */
		public static final ValueAdaptor asDouble = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				double re = rs.getDouble(colName);
				return rs.wasNull() ? null : re;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.DOUBLE);
				} else {
					double v;
					if (obj instanceof Number)
						v = ((Number) obj).doubleValue();
					else
						v = Castors.me().castTo(obj.toString(), double.class);
					stat.setDouble(i, v);
				}
			}
		};

		/**
		 * 日历适配器
		 */
		public static final ValueAdaptor asCalendar = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				Timestamp ts = rs.getTimestamp(colName);
				if (null == ts)
					return null;
				Calendar c = Calendar.getInstance();
				c.setTimeInMillis(ts.getTime());
				return c;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.TIMESTAMP);
				} else {
					Timestamp v;
					if (obj instanceof Calendar)
						v = new Timestamp(((Calendar) obj).getTimeInMillis());
					else
						v = Castors.me().castTo(obj, Timestamp.class);
					stat.setTimestamp(i, v);
				}
			}
		};

		/**
		 * 时间戳适配器
		 */
		public static final ValueAdaptor asTimestamp = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				return rs.getTimestamp(colName);
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.TIMESTAMP);
				} else {
					Timestamp v;
					if (obj instanceof Timestamp)
						v = (Timestamp) obj;
					else
						v = Castors.me().castTo(obj, Timestamp.class);
					stat.setTimestamp(i, v);
				}
			}
		};

		/**
		 * 日期适配器
		 */
		public static final ValueAdaptor asDate = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				Timestamp ts = rs.getTimestamp(colName);
				return null == ts ? null : new java.util.Date(ts.getTime());
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				Timestamp v;
				if (obj instanceof java.util.Date)
					v = new Timestamp(((java.util.Date) obj).getTime());
				else
					v = Castors.me().castTo(obj, Timestamp.class);
				stat.setTimestamp(i, v);
			}
		};

		/**
		 * Sql 日期适配器
		 */
		public static final ValueAdaptor asSqlDate = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				return rs.getDate(colName);
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.DATE);
				} else {
					java.sql.Date v;
					if (obj instanceof java.sql.Date)
						v = (java.sql.Date) obj;
					else
						v = Castors.me().castTo(obj, java.sql.Date.class);
					stat.setDate(i, v);
				}
			}
		};

		/**
		 * Sql 时间适配器
		 */
		public static final ValueAdaptor asSqlTime = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				return rs.getTime(colName);
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				java.sql.Time v;
				if (null == obj) {
					stat.setNull(i, Types.TIME);
				} else {
					if (obj instanceof java.sql.Time)
						v = (java.sql.Time) obj;
					else
						v = Castors.me().castTo(obj, java.sql.Time.class);
					stat.setTime(i, v);
				}
			}
		};

		/**
		 * 数字枚举适配器
		 */
		public static final ValueAdaptor asEnumInt = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				int re = rs.getInt(colName);
				return rs.wasNull() ? null : re;
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setNull(i, Types.INTEGER);
				} else {
					int v;
					if (obj instanceof Enum<?>)
						v = ((Enum<?>) obj).ordinal();
					else
						v = Castors.me().castTo(obj, int.class);
					stat.setInt(i, v);
				}
			}
		};

		/**
		 * 字符枚举适配器
		 */
		public static final ValueAdaptor asEnumChar = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				return rs.getString(colName);
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setString(i, null);
				} else {
					String v = obj.toString();
					stat.setString(i, v);
				}
			}
		};

		/**
		 * 默认对象适配器
		 */
		public static final ValueAdaptor asObject = new ValueAdaptor() {
			public Object get(ResultSet rs, String colName) throws SQLException {
				return rs.getObject(colName);
			}

			public void set(PreparedStatement stat, Object obj, int i) throws SQLException {
				if (null == obj) {
					stat.setString(i, null);
				} else {
					String v = Castors.me().castToString(obj);
					stat.setString(i, v);
				}
			}
		};
	}

	/**
	 * 根据字段现有的信息，尽可能猜测一下字段的数据库类型
	 * 
	 * @param ef
	 *            映射字段
	 * @param info
	 *            字段配置信息
	 */
	public static void guessEntityFieldColumnType(NutMappingField ef) {
		Mirror<?> mirror = ef.getTypeMirror();

		// 整型
		if (mirror.isInt()) {
			ef.setColumnType(ColType.INT);
			ef.setWidth(8);
		}
		// 字符串
		else if (mirror.isStringLike() || mirror.is(Email.class)) {
			ef.setColumnType(ColType.VARCHAR);
			ef.setWidth(50);
		}
		// 长整型
		else if (mirror.isLong()) {
			ef.setColumnType(ColType.INT);
			ef.setWidth(16);
		}
		// 枚举
		else if (mirror.isEnum()) {
			ef.setColumnType(ColType.VARCHAR);
			ef.setWidth(20);
		}
		// 时间戳
		else if (mirror.is(Timestamp.class)) {
			ef.setColumnType(ColType.TIMESTAMP);
		}
		// 布尔
		else if (mirror.isBoolean()) {
			ef.setColumnType(ColType.BOOLEAN);
			ef.setWidth(1);
		}
		// 字符
		else if (mirror.isChar()) {
			ef.setColumnType(ColType.CHAR);
			ef.setWidth(20);
		}
		// 日期
		else if (mirror.is(java.sql.Date.class)) {
			ef.setColumnType(ColType.DATE);
		}
		// 时间
		else if (mirror.is(java.sql.Time.class)) {
			ef.setColumnType(ColType.TIME);
		}
		// 日期时间
		else if (mirror.isOf(Calendar.class) || mirror.is(java.util.Date.class)) {
			ef.setColumnType(ColType.DATETIME);
		}
		// 大数
		else if (mirror.is(BigDecimal.class)) {
			ef.setColumnType(ColType.INT);
			ef.setWidth(32);
		}
		// 短整型
		else if (mirror.isShort()) {
			ef.setColumnType(ColType.INT);
			ef.setWidth(4);
		}
		// 字节
		else if (mirror.isByte()) {
			ef.setColumnType(ColType.INT);
			ef.setWidth(2);
		}
		// 浮点
		else if (mirror.isFloat()) {
			ef.setColumnType(ColType.FLOAT);
		}
		// 双精度浮点
		else if (mirror.isDouble()) {
			ef.setColumnType(ColType.FLOAT);
		}
		// 文本流
		else if (mirror.isOf(Reader.class) || mirror.isOf(Clob.class)) {
			ef.setColumnType(ColType.TEXT);
		}
		// 二进制流
		else if (mirror.isOf(InputStream.class)
					|| mirror.is(byte[].class)
					|| mirror.isOf(Blob.class)) {
			ef.setColumnType(ColType.BINARY);
		}
		/*
		 * 上面的都不是？ 那就当作字符串好了，反正可以 toString
		 */
		else {
			if (log.isDebugEnabled())
				log.debugf(	"take field '%s(%s)'(%s) as VARCHAR(50)",
							ef.getName(),
							Lang.getTypeClass(ef.getType()).getName(),
							ef.getEntity().getType().getName());
			ef.setColumnType(ColType.VARCHAR);
			ef.setWidth(50);
		}
	}

}
